from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text, select
import time
from flask_migrate import Migrate


app = Flask(__name__)


USERNAME = 'root'

PASSWORD = '12345678'

HOSTNAME = '127.0.0.1'

PORT = 3306

DATABASE = 'test_database'

# app.config['SQLALCHEMY_DATABASE_URI'] = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4'
app.config['SQLALCHEMY_DATABASE_URI'] = f'mysql+mysqldb://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4'

db = SQLAlchemy(app)

mg = Migrate(app,db)

# with app.app_context():
#     with db.engine.connect() as con:
#         res = con.execute(text('select 1'))
#         # stmt =select(1)
#         # res= con.execute(stmt)
#         print(res.fetchone())

@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'

@app.route('/user/add')
def add_user():
    us = f'test{time.time()}'
    user = User(username=us, email='test@example.com')
    db.session.add(user)
    db.session.commit()
    return 'User added successfully'

@app.route('/user/query')
def query_user():
    user = User.query.all()
    for u in user:
        print(u.username, u.email)
    # return f'User: {user.username}, Email: {user.email}'
    return 'User query successfully'

@app.route('/user/update')
def update_user():
    user = User.query.filter_by(username='test')[0]
    user.username = 'test2'
    db.session.commit()
    return 'User updated successfully'


@app.route('/user/delete')
def delete_user():
    user = User.query.filter_by(username='test2').first()
    db.session.delete(user)
    db.session.commit()
    return 'User deleted successfully'

@app.route('/user/query_all_articles')
def query_all_articles():
    user = User.query.get(2)
    for article in user.articles:
        print(article.title, article.content)
    return 'All articles query successfully'

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), nullable=False)
    age = db.Column(db.Integer, nullable=False)
    # address = db.Column(db.String(120), nullable=False)
    # 将外键字段与主键关联起来
    articles = db.relationship('Artictle', back_populates='author')

class Artictle(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80), nullable=False)
    content = db.Column(db.String(120), nullable=False)
    # link = db.Column(db.String(120), nullable=False)
    # 建立外键约束
    authour_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    # 将外键字段与主键关联起来
    author = db.relationship('User', back_populates='articles')
    # author = db.relationship('User', backref='articles')

with app.app_context():
    # db.create_all()
    article1 = Artictle(title='testtitle2', content='testcontent2')
    article1.author = User.query.get(2)
    # ar = Artictle.query.filter_by(title='testtitle1').first()
    article2 = Artictle(title='testtitle3', content='testcontent3')
    article2.author = User.query.get(2)

    # 一次添加一条数据到session中
    # db.session.add(article)
    # 一次添加多条数据到session中
    # db.session.add_all([article1, article2])
    # db.session.delete(ar)
    # 提交session中的数据
    # db.session.commit()


# 更新表字段步骤
# mg= Migrate(app, db)
# 终端中依次执行：
#  flask db init 只需要执行一次
#  flask db migrate
#  flask db upgrade
if __name__ == '__main__':
    app.run()
